// conformingLoans.do
// Inputs: cll_YYYY.xls(x)
// Outputs: conforming_linit_all
// Date last updated: 1/27/2025

// This do file compiles county-by-year loan limit information from the Federal Housing Finance Agency (FHFA). 
// We collect data from 2009-2016 here: https://www.fhfa.gov/data/conforming-loan-limit

********************************************************************************	
*  Clean each year of data and save as different file
********************************************************************************
// 2009-2012, create fips code and year vars, drop unnecessary vars
foreach year in 2009 2010 2011 2012{
	import excel "$pathr\cll_`year'.xls", sheet("GSE_limits") cellrange(A2:I3235) firstrow clear
	gen fips_code = FIPSStateCode + FIPSCountyCode
	drop FIPSStateCode FIPSCountyCode CountyName CBSANumber State
	gen sale_year = `year'
	destring fips_code, replace
	
	* Save temporary file
	tempfile cll`year'
	save `cll`year''
}

// 2013-2014, create fips code and year vars, drop unnecessary vars
foreach year in 2013 2014{
	import excel "$pathr\cll_`year'.xls", sheet("GSE Limits") cellrange(A2:I3235) firstrow clear
	gen fips_code = FIPSStateCode + FIPSCountyCode
	drop FIPSStateCode FIPSCountyCode CountyName CBSANumber State
	gen sale_year = `year'
	destring fips_code, replace
	
	* Save temporary file
	tempfile cll`year'
	save `cll`year''
}

// 2015-2016, create fips code and year vars, drop unnecessary vars
foreach year in 2015 2016{
	import excel "$pathr\cll_`year'.xlsx", sheet("GSE Limits") cellrange(A2:I3235) firstrow clear
	gen fips_code = FIPSStateCode + FIPSCountyCode
	drop FIPSStateCode FIPSCountyCode CountyName CBSANumber State
	gen sale_year = `year'
	destring fips_code, replace

	* save temporary file
	tempfile cll`year'
	save `cll`year''
}


// Append all the years and save final dataset
	use `cll2009', clear
	forval year = 2010(1)2016 {
		append using `cll`year''
	}

	save "$pathi\conforming_limit_all", replace